package com.cff.shorturl.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.cff.shorturl.task.CallbackTask;
import com.cff.shorturl.util.Utils;

public abstract class DbManager {
	final Logger logger = LogManager.getLogger(DbManager.class);

	static Properties properties = null;
	static String db = "";
	static DbManager dbManager = null;
	static {
		properties = Utils.loadPropertiesFile("db.properties");
		db = properties.getProperty("db");
		if (db.equalsIgnoreCase("druid.mysql")) {
			dbManager = new DruidDbManager();
		} else if (db.equalsIgnoreCase("dbcp.mysql")) {
			dbManager = new DbcpDbManager();
		} else if (db.equalsIgnoreCase("dbcp.c3p0")) {
			dbManager = new C3p0DbManager();
		}
	}

	public static DbManager getInstance() {
		return dbManager;
	}

	/**
	 * 返回druid数据库连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public abstract Connection getConnection() throws SQLException;

	public String queryString(String sql, Object[] params) throws SQLException {
		return queryString(getConnection(), sql, params);
	}

	public int queryInt(String sql, Object[] params) throws SQLException {
		return queryInt(getConnection(), sql, params);
	}
	
	public List<Map<String, String>> query(String sql, Object[] params) throws SQLException {
		return query(getConnection(), sql, params);
	}

	public void execute(String sql, Object... params) throws SQLException {
		execute(getConnection(), sql, params);
	}

	/**
	 * 获取一个字符串
	 * 
	 * @param con
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public String queryString(Connection con, String sql, Object[] params) throws SQLException {
		PreparedStatement pre = null;
		try {
			pre = con.prepareStatement(sql);
			// 参数赋值
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pre.setObject(i + 1, params[i]);
				}
			}
			ResultSet resultSet = pre.executeQuery();
			while (resultSet.next()) {
				String value = resultSet.getString(1);
				return value;
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				if (pre != null)
					pre.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 插入或者更新
	 * 
	 * @param con
	 * @param sql
	 * @param params
	 * @throws SQLException
	 */
	public void execute(Connection con, String sql, Object... params) throws SQLException {
		String par = "";
		for (Object o : params) {
			par += "," + o.toString();
		}

		logger.info("准备执行sql: {}::{}", sql, par);

		PreparedStatement pre = null;
		try {
			pre = con.prepareStatement(sql);
			// 参数赋值
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pre.setObject(i + 1, params[i]);
				}
			}
			// 执行
			pre.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				if (pre != null)
					pre.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取一个int
	 * 
	 * @param con
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public int queryInt(Connection con, String sql, Object[] params) throws SQLException {
		PreparedStatement pre = null;
		try {
			String par = "";
			for (Object o : params) {
				par += "," + o.toString();
			}
			logger.info("准备执行sql: {}::{}", sql, par);
			
			pre = con.prepareStatement(sql);
			// 参数赋值
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pre.setObject(i + 1, params[i]);
				}
			}
			ResultSet resultSet = pre.executeQuery();
			while (resultSet.next()) {
				int value = resultSet.getInt(1);
				return value;
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			try {
				if (pre != null)
					pre.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	public List<Map<String, String>> query(Connection con, String sql, Object[] params) {
		PreparedStatement pre = null;
		try {
			pre = con.prepareStatement(sql);

			// 参数赋值
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pre.setObject(i + 1, params[i]);
				}
			}

			// 执行
			ResultSet resultSet = pre.executeQuery();
			List<Map<String, String>> list = new ArrayList<Map<String, String>>();
			while (resultSet.next()) {
				Map<String, String> hm = new HashMap<String, String>();
				ResultSetMetaData rsmd = resultSet.getMetaData();
				int count = rsmd.getColumnCount();
				for (int i = 1; i <= count; i++) {
					String key = rsmd.getColumnLabel(i);
					String value = resultSet.getString(i);
					hm.put(key, value);
				}
				list.add(hm);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (pre != null)
					pre.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
